<?php
class HomeIndexController extends Zone_Action{
	public function indexAction(){
		$this->removeLayout();
		$user_id = get_user_id();
		
		$user = $this->Model->fetchRow("SELECT * FROM `users` WHERE `ID`='$user_id' LIMIT 1");
		
		$this->set(array(
			user	=> $user,
			users	=> $this->Model->fetchAll("SELECT * 
				FROM `users` 
				WHERE DATE(`date_login`) >= DATE(NOW()) ORDER BY `date_login`")
		));		
		
		
		$this->getNews();
		$this->getProjects();
		$this->getCalendar();
		$this->getTask();
		$this->getCrm();
	}
	
	protected function getNews(){
		$news = $this->Model->fetchAll("SELECT * 
			FROM `news` 
			WHERE `date`<= NOW() 
				AND `is_draft`='no' 
			ORDER BY `date` DESC
			LIMIT 0,8");
		$this->set('news',$news);
	}
	
	protected function getProjects(){
		$projects = $this->Model->fetchAll("SELECT * FROM `projects` 
			WHERE `is_draft`='no' AND `status`<>'complete' LIMIT 0,10");
		$this->set('projects',$projects);
	}
	
	protected function getCalendar(){
		$d = date('Y-m-d');
		$time = strtotime($d);
		$year = (int)get('year',$time);
		$week = (int)get('week',$time);
		
		$user_id = get_user_id();
		$posts 
			= $this->Model->fetchAll("SELECT 
					*
				FROM `calendars` 
				WHERE 
					`type`='company' 
				AND `date_start` >= '$d'	
				AND (
					( `repeat_type`='none' AND `date_start` >= '$d'  )
				)	
				ORDER BY DATE(CONCAT(`date_start`,' ',`time_start`)) ");	
				
					/*OR (
						( `repeat_date_end` IS NULL OR `repeat_date_end` >= '$d' )
						AND(
							( `repeat_type`='day' AND CONCAT(',',`repeat_day`,',') LIKE '%,".((date('N',$time)+1)%7).",%' ) 
							OR ( `repeat_type`='week'  AND DAYOFWEEK(`date_start`)  = '".((date('N',$time)+1)%7)."'  ) 
							OR ( `repeat_type`='month' AND DAYOFMONTH(`date_start`) = DAYOFMONTH('$d')  )
							OR ( `repeat_type`='year'  AND DAYOFMONTH(`date_start`) = DAYOFMONTH('$d') AND MONTH(`date_start`) = MONTH('$d') ) 
						)
					)*/				
				
		$this->set('calendars',$posts);			
	}
	
	protected function getCrm(){
		$user_id = get_user_id();
		$posts = $this->Model->fetchAll("SELECT 
				`name`,`ID` 
			FROM `crm_customers` as `a`
			WHERE `a`.`is_draft`='no'
			ORDER BY `a`.`date_created` DESC LIMIT 8"
		);	
		$this->set('crms',$posts);			
	}
	
	protected function getTask(){
		$user_id = get_user_id();
		$posts = $this->Model->fetchAll("SELECT 
				`a`.*
			FROM `tasks` as `a` 
			WHERE `a`.`is_draft`='no'
			ORDER BY `date_updated` DESC
			LIMIT 8");

		$total_task_deadline = $this->Model->fetchOne("SELECT COUNT(`a`.`ID`) as `total` 				
			FROM `tasks` as `a` 
			LEFT JOIN `tasks_users` as `d` 
				ON `a`.`ID`=`d`.`task_id` AND `d`.`user_id`='$user_id' 
			WHERE 
				`a`.`is_draft`='no' 
				AND (	`d`.`user_id` IS NOT NULL OR `a`.`created_by_id`='$user_id' ) 
				AND (`a`.`status`='active' AND `a`.`date_end` <= NOW() )");

		$total_task_new = $this->Model->fetchOne("SELECT COUNT(*) as `total` 				
			FROM `tasks` as `a` 
			LEFT JOIN `tasks_users` as `d` 
				ON `a`.`ID`=`d`.`task_id` AND `d`.`user_id`='$user_id'
			WHERE 
				`a`.`is_draft`='no' 
				AND ( `d`.`user_id` IS NOT NULL OR `a`.`created_by_id`='$user_id' )
				AND NOT EXISTS( SELECT * FROM `tasks_reads` as `b` 
					WHERE `a`.`ID`=`b`.`task_id` AND `b`.`user_id`='$user_id')");
			
		$this->set(array(
			tasks				=> $posts,
			total_task_deadline => $total_task_deadline,
			total_task_new		=> $total_task_new
		));	
	}
	

}	